﻿using System;
using System.Collections.Generic;
using System.Text;
using BarCode.Module;
using BarCode.Common;
using BarCode.Bean;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace BarCode.AccessDao
{
    public class BarCodeMdbDao
    {
        private static readonly log4net.ILog LogUtil = log4net.LogManager.GetLogger(typeof(BarCodeMdbDao));

        private AccessHelper barcodeMdb;
        public BarCodeMdbDao()
        {
            barcodeMdb = new AccessHelper(Consts.MDB_BARCODE);
        }

        public BarCodeMdbDao(string barCodeMdbName)
        {
            barcodeMdb = new AccessHelper(barCodeMdbName);
        }

        public void Release()
        {
            barcodeMdb.Release();
        }

        /// <summary>
        /// 获取基本二维码信息
        /// </summary>
        /// <returns></returns>
        public List<BarCodeInfo> getBarCodeList()
        {
            List<BarCodeInfo> retList = new List<BarCodeInfo>();
            DataSet retSet = barcodeMdb.Query("select * from base_barcode");
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow tmpRow in dt.Rows)
                    {
                        BarCodeInfo tmpInfo = new BarCodeInfo();
                        tmpInfo.barcodeID = Convert.ToString(tmpRow["barcodeID"]);
                        tmpInfo.Operator_Name = Convert.ToString(tmpRow["Operator_Name"]);
                        tmpInfo.OpDt = Convert.ToDateTime(tmpRow["OpDt"]);
                        tmpInfo.mp_number = Convert.ToString(tmpRow["mp_number"]);
                        tmpInfo.boxid = Convert.ToString(tmpRow["boxid"]);
                        tmpInfo.part1 = Convert.ToString(tmpRow["part1"]);
                        tmpInfo.part2 = Convert.ToString(tmpRow["part2"]);
                        tmpInfo.part3 = Convert.ToString(tmpRow["part3"]);
                        retList.Add(tmpInfo);
                    }
                }
            }
            return retList;
        }


        /// <summary>
        /// 根据ID获取BarCode
        /// </summary>
        /// <param name="barcodeId"></param>
        /// <returns></returns>
        public BarCodeInfo getBarCodeById(string barcodeId)
        {
            OleDbParameter[] sqlParams = new OleDbParameter[1];
            sqlParams[0] = new OleDbParameter("barcodeID", barcodeId);
            DataSet retSet = barcodeMdb.Query("select * from base_barcode where [barcodeID] = @barcodeID", sqlParams);
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    DataRow tmpRow = dt.Rows[0];
                    BarCodeInfo tmpInfo = new BarCodeInfo();
                    tmpInfo.barcodeID = Convert.ToString(tmpRow["barcodeID"]);
                    tmpInfo.Operator_Name = Convert.ToString(tmpRow["Operator_Name"]);
                    tmpInfo.OpDt = Convert.ToDateTime(tmpRow["OpDt"]);
                    tmpInfo.mp_number = Convert.ToString(tmpRow["mp_number"]);
                    tmpInfo.boxid = Convert.ToString(tmpRow["boxid"]);
                    tmpInfo.part1 = Convert.ToString(tmpRow["part1"]);
                    tmpInfo.part2 = Convert.ToString(tmpRow["part2"]);
                    tmpInfo.part3 = Convert.ToString(tmpRow["part3"]);
                    return tmpInfo;
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 插入二维码信息
        /// </summary>
        /// <param name="lstLicence"></param>
        /// <returns></returns>
        public int insertBarCode(BarCodeInfo barCodeInfo)
        {
            string insBarCodeSql = "INSERT INTO [base_barcode] ([barcodeID], [Operator_Name], [OpDt], [mp_number], [boxid], [part1], [part2], [part3]) VALUES (@barcodeID, @Operator_Name, @OpDt, @mp_number, @boxid, @part1, @part2, @part3)";
            OleDbParameter[] sqlParams = new OleDbParameter[8];
            sqlParams[0] = new OleDbParameter("@barcodeID", barCodeInfo.barcodeID);
            sqlParams[1] = new OleDbParameter("@Operator_Name", barCodeInfo.Operator_Name);
            sqlParams[2] = new OleDbParameter("@OpDt", barCodeInfo.OpDt.ToString());
            sqlParams[3] = new OleDbParameter("@mp_number", barCodeInfo.mp_number);
            sqlParams[4] = new OleDbParameter("@boxid", barCodeInfo.boxid);
            sqlParams[5] = new OleDbParameter("@part1", barCodeInfo.part1);
            sqlParams[6] = new OleDbParameter("@part2", barCodeInfo.part2);
            sqlParams[7] = new OleDbParameter("@part3", barCodeInfo.part3);
            int insCnt = barcodeMdb.ExecuteSql(insBarCodeSql, sqlParams);
            LogUtil.Debug("插入本地二维码信息");
            return insCnt;
        }

        /// <summary>
        /// 更新二维码信息
        /// </summary>
        /// <param name="barCodeInfo"></param>
        /// <returns></returns>
        public int updateBarCode(BarCodeInfo barCodeInfo)
        {
            string updBarCodeSql = "UPDATE [base_barcode] SET [mp_number] = @mp_number, [boxid] = @boxid, [part1] = @part1 , [part2] = @part2 , [part3] = @part3 where [barcodeID] = @barcodeID AND [Operator_Name] = @Operator_Name AND Format([OpDt],'yyyy-mm-dd') = @OpDt";
            OleDbParameter[] sqlParams = new OleDbParameter[8];
            sqlParams[0] = new OleDbParameter("@mp_number", barCodeInfo.mp_number);
            sqlParams[1] = new OleDbParameter("@boxid", barCodeInfo.boxid);
            sqlParams[2] = new OleDbParameter("@part1", barCodeInfo.part1);
            sqlParams[3] = new OleDbParameter("@part2", barCodeInfo.part2);
            sqlParams[4] = new OleDbParameter("@part3", barCodeInfo.part3);
            sqlParams[5] = new OleDbParameter("@barcodeID", barCodeInfo.barcodeID);
            sqlParams[6] = new OleDbParameter("@Operator_Name", barCodeInfo.Operator_Name);
            sqlParams[7] = new OleDbParameter("@OpDt", barCodeInfo.OpDt.ToString("yyyy-MM-dd"));   
            int updCnt = barcodeMdb.ExecuteSql(updBarCodeSql, sqlParams);
            LogUtil.Debug("更新本地二维码信息");
            return updCnt;
        }

        /// <summary>
        /// 删除二维码信息
        /// </summary>
        /// <returns></returns>
        public int deleteBaseBarCode()
        {
            string delSql = "delete from [base_barcode] ";
            return barcodeMdb.ExecuteSql(delSql, new OleDbParameter[]{});
        }


        /// <summary>
        /// 获取指定用户当日的工作量
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public int getWorkAmount(string userName)
        {
            OleDbParameter[] sqlParams = new OleDbParameter[2];
            sqlParams[0] = new OleDbParameter("@Operator_Name", userName);
            sqlParams[1] = new OleDbParameter("@OpDt", DateTime.Now.ToString("yyyy-MM-dd"));
            string amountSql = "select count(1) from base_barcode where [Operator_Name]=@Operator_Name AND Format([OpDt],'yyyy-mm-dd') = @OpDt";
            DataSet retSet = barcodeMdb.Query(amountSql, sqlParams);
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0][0]);
                }
                else
                {
                    return 0;
                }
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 获取指定用户当日的工作量
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public int getJXJKWorkAmount(string userName)
        {
            OleDbParameter[] sqlParams = new OleDbParameter[2];
            sqlParams[0] = new OleDbParameter("@Operator_Name", userName);
            sqlParams[1] = new OleDbParameter("@OpDt", DateTime.Now.ToString("yyyy-MM-dd"));
            string amountSql = "select count(1) from jxjk_barcode where [Operator_Name]=@Operator_Name AND Format([OpDt],'yyyy-mm-dd') = @OpDt and [mark_repair] ='合格'";
            DataSet retSet = barcodeMdb.Query(amountSql, sqlParams);
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0][0]);
                }
                else
                {
                    return 0;
                }
            }
            else
            {
                return 0;
            }
        }


        /// <summary>
        /// 获取指定用户当日的工作量
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public int getJKWorkAmount(string userName)
        {
            OleDbParameter[] sqlParams = new OleDbParameter[2];
            sqlParams[0] = new OleDbParameter("@Operator_Name", userName);
            sqlParams[1] = new OleDbParameter("@OpDt", DateTime.Now.ToString("yyyy-MM-dd"));
            string amountSql = "select count(1) from jk_barcode where [Operator_Name]=@Operator_Name AND Format([OpDt],'yyyy-mm-dd') = @OpDt ";
            DataSet retSet = barcodeMdb.Query(amountSql, sqlParams);
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0][0]);
                }
                else
                {
                    return 0;
                }
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 获取指定用户当日的封装工作量
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public int getPackWorkAmount(string userName)
        {
            OleDbParameter[] sqlParams = new OleDbParameter[3];
            sqlParams[0] = new OleDbParameter("@Operator_Name", userName);
            if (DateTime.Now.ToString("HHmmss").CompareTo(GlobalVal.WORK_START_TIME) < 0)
            {
                sqlParams[1] = new OleDbParameter("@StartOpDt", DateTime.Now.AddDays(-1).ToString("yyyyMMdd") + GlobalVal.WORK_START_TIME);
                sqlParams[2] = new OleDbParameter("@EndOpDt", DateTime.Now.ToString("yyyyMMdd") + GlobalVal.WORK_START_TIME);
            }
            else
            {
                sqlParams[1] = new OleDbParameter("@StartOpDt", DateTime.Now.ToString("yyyyMMdd") + GlobalVal.WORK_START_TIME);
                sqlParams[2] = new OleDbParameter("@EndOpDt", DateTime.Now.AddDays(1).ToString("yyyyMMdd") + GlobalVal.WORK_START_TIME);
            }
            
            string amountSql = "select count(1) from pack_barcode where [Operator_Name]=@Operator_Name AND Format([OpDt],'yyyymmDdHhNnSs') >= @StartOpDt AND Format([OpDt],'yyyymmDdHhNnSs') <= @EndOpDt and [delbz]='0'";
            DataSet retSet = barcodeMdb.Query(amountSql, sqlParams);
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0][0]);
                }
                else
                {
                    return 0;
                }
            }
            else
            {
                return 0;
            }
        }


        /// <summary>
        /// 获取机芯机壳二维码信息
        /// </summary>
        /// <returns></returns>
        public List<JXJKBarCodeInfo> getJxJkBarCodeList()
        {
            List<JXJKBarCodeInfo> retList = new List<JXJKBarCodeInfo>();
            DataSet retSet = barcodeMdb.Query("select * from jxjk_barcode");
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow tmpRow in dt.Rows)
                    {
                        JXJKBarCodeInfo tmpInfo = new JXJKBarCodeInfo();
                        tmpInfo.JxBarcodeId = Convert.ToString(tmpRow["jx_barcodeID"]);
                        tmpInfo.JkBarcodeId = Convert.ToString(tmpRow["jk_barcodeID"]);
                        tmpInfo.VavleBarcodeId = Convert.ToString(tmpRow["vavle_barcodeID"]);
                        tmpInfo.OperatorName = Convert.ToString(tmpRow["Operator_Name"]);
                        tmpInfo.OpDt = Convert.ToDateTime(tmpRow["OpDt"]);
                        tmpInfo.markRepair = Convert.ToString(tmpRow["mark_repair"]);
                        tmpInfo.UpShellCode = Convert.ToString(tmpRow["up_shell"]);
                        tmpInfo.DownShellCode = Convert.ToString(tmpRow["down_shell"]);
                        tmpInfo.frame1Code = Convert.ToString(tmpRow["frame1"]);
                        tmpInfo.frame2Code = Convert.ToString(tmpRow["frame2"]);
                        retList.Add(tmpInfo);
                    }
                }
            }
            return retList;
        }

        /// <summary>
        /// 插入机芯机壳二维码信息
        /// </summary>
        /// <param name="lstLicence"></param>
        /// <returns></returns>
        public int insertJXJKBarCode(JXJKBarCodeInfo barCodeInfo)
        {
            string insBarCodeSql = "INSERT INTO [jxjk_barcode] ([jx_barcodeID], [jk_barcodeID], [vavle_barcodeID], [Operator_Name], [OpDt], [mark_repair], [up_shell], [down_shell], [frame1], [frame2]) VALUES (@jx_barcodeID, @jk_barcodeID, @vavle_barcodeID, @Operator_Name, @OpDt, @mark_repair, @UpShellCode, @DownShellCode,@frame1,@frame2)";
            OleDbParameter[] sqlParams = new OleDbParameter[10];
            sqlParams[0] = new OleDbParameter("@jx_barcodeID", barCodeInfo.JxBarcodeId);
            sqlParams[1] = new OleDbParameter("@jk_barcodeID", barCodeInfo.JkBarcodeId);
            sqlParams[2] = new OleDbParameter("@jk_barcodeID", barCodeInfo.VavleBarcodeId);
            sqlParams[3] = new OleDbParameter("@Operator_Name", barCodeInfo.OperatorName);
            sqlParams[4] = new OleDbParameter("@OpDt", barCodeInfo.OpDt.ToString());
            sqlParams[5] = new OleDbParameter("@mark_repair", barCodeInfo.markRepair);
            sqlParams[6] = new OleDbParameter("@UpShellCode", barCodeInfo.UpShellCode);
            sqlParams[7] = new OleDbParameter("@DownShellCode", barCodeInfo.DownShellCode);
            sqlParams[8] = new OleDbParameter("@frame1", barCodeInfo.frame1Code);
            sqlParams[9] = new OleDbParameter("@frame2", barCodeInfo.frame2Code);
            return barcodeMdb.ExecuteSql(insBarCodeSql, sqlParams);
        }

        /// <summary>
        /// 删除重复的机芯机壳二维码信息
        /// </summary>
        /// <param name="barCodeInfo"></param>
        /// <returns></returns>
        public int deleteJXJKBarCode(JXJKBarCodeInfo barCodeInfo)
        {
            string delSql = "delete from [jxjk_barcode] where ([jx_barcodeID] = @jx_barcodeID or [jk_barcodeID] = @jk_barcodeID) and [mark_repair]='合格'";
            OleDbParameter[] sqlParams = new OleDbParameter[2];
            sqlParams[0] = new OleDbParameter("@jx_barcodeID", barCodeInfo.JxBarcodeId);
            sqlParams[1] = new OleDbParameter("@jk_barcodeID", barCodeInfo.JkBarcodeId);
            return barcodeMdb.ExecuteSql(delSql, sqlParams);
        }

        /// <summary>
        /// 删除机芯机壳二维码信息
        /// </summary>
        /// <returns></returns>
        public int deleteJXJKBarCode()
        {
            string delSql = "delete from [jxjk_barcode] ";
            return barcodeMdb.ExecuteSql(delSql, new OleDbParameter[] { });
        }

        /// <summary>
        /// 更新返修状态
        /// </summary>
        /// <param name="barCodeInfo"></param>
        public int updateJKRepairStatus(JXJKBarCodeInfo barCodeInfo)
        {
            int existCnt = 0;
            OleDbParameter existParam = new OleDbParameter("@jk_barcodeID", barCodeInfo.JkBarcodeId);
            string amountSql = "select count(1) from jxjk_barcode where [jk_barcodeID]=@jk_barcodeID and [mark_repair] ='合格'";
            DataSet retSet = barcodeMdb.Query(amountSql, new OleDbParameter[] { existParam });
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    existCnt =  Convert.ToInt32(dt.Rows[0][0]);
                }
            }
            if (existCnt > 0)
            {
                // 更新
                LogUtil.Debug("返修记录存在即更新：" + barCodeInfo.JkBarcodeId);
                string updBarCodeSql = "UPDATE [jxjk_barcode] SET [mark_repair] = '返修', [OpDt] = @OpDt where [jk_barcodeID]=@jk_barcodeID and [mark_repair] ='合格' ";
                OleDbParameter[] sqlParams = new OleDbParameter[2];
                sqlParams[0] = new OleDbParameter("@OpDt", barCodeInfo.OpDt.ToString());
                sqlParams[1] = new OleDbParameter("@jk_barcodeID", barCodeInfo.JkBarcodeId);
                return barcodeMdb.ExecuteSql(updBarCodeSql, sqlParams);
            }
            else
            {
                // 插入
                LogUtil.Debug("返修记录不存在即插入：" + barCodeInfo.JkBarcodeId);
                string insBarCodeSql = "INSERT INTO [jxjk_barcode] ([jx_barcodeID], [jk_barcodeID], [Operator_Name], [OpDt], [mark_repair]) VALUES (@jx_barcodeID, @jk_barcodeID, @Operator_Name, @OpDt, @mark_repair)";
                OleDbParameter[] sqlParams = new OleDbParameter[5];
                sqlParams[0] = new OleDbParameter("@jx_barcodeID", barCodeInfo.JxBarcodeId);
                sqlParams[1] = new OleDbParameter("@jk_barcodeID", barCodeInfo.JkBarcodeId);
                sqlParams[2] = new OleDbParameter("@Operator_Name", barCodeInfo.OperatorName);
                sqlParams[3] = new OleDbParameter("@OpDt", barCodeInfo.OpDt.ToString());
                sqlParams[4] = new OleDbParameter("@mark_repair", barCodeInfo.markRepair);
                return barcodeMdb.ExecuteSql(insBarCodeSql, sqlParams);
            }
        }


        /// <summary>
        /// 获取基本二维码信息
        /// </summary>
        /// <returns></returns>
        public List<JKBarCodeInfo> getJKBarCodeList()
        {
            List<JKBarCodeInfo> retList = new List<JKBarCodeInfo>();
            DataSet retSet = barcodeMdb.Query("select * from jk_barcode");
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow tmpRow in dt.Rows)
                    {
                        JKBarCodeInfo tmpInfo = new JKBarCodeInfo();
                        tmpInfo.JkBarcodeId = Convert.ToString(tmpRow["jk_barcodeID"]);
                        tmpInfo.OperatorName = Convert.ToString(tmpRow["Operator_Name"]);
                        tmpInfo.OpDt = Convert.ToDateTime(tmpRow["OpDt"]);
                        retList.Add(tmpInfo);
                    }
                }
            }
            return retList;
        }


        /// <summary>
        /// 删除重复的机壳信息
        /// </summary>
        /// <param name="barCodeInfo"></param>
        /// <returns></returns>
        public int deleteJKBarCode(JKBarCodeInfo barCodeInfo)
        {
            string delSql = "delete from [jk_barcode] where [jk_barcodeID] = @jk_barcodeID";
            OleDbParameter[] sqlParams = new OleDbParameter[1];
            sqlParams[0] = new OleDbParameter("@jk_barcodeID", barCodeInfo.JkBarcodeId);
            return barcodeMdb.ExecuteSql(delSql, sqlParams);
        }

        /// <summary>
        /// 插入机壳信息
        /// </summary>
        /// <param name="lstLicence"></param>
        /// <returns></returns>
        public int insertJKBarCode(JKBarCodeInfo barCodeInfo)
        {
            string insBarCodeSql = "INSERT INTO [jk_barcode] ([jk_barcodeID], [Operator_Name], [OpDt]) VALUES (@jk_barcodeID, @Operator_Name, @OpDt)";
            OleDbParameter[] sqlParams = new OleDbParameter[3];
            sqlParams[0] = new OleDbParameter("@jk_barcodeID", barCodeInfo.JkBarcodeId);
            sqlParams[1] = new OleDbParameter("@Operator_Name", barCodeInfo.OperatorName);
            sqlParams[2] = new OleDbParameter("@OpDt", barCodeInfo.OpDt.ToString());
            return barcodeMdb.ExecuteSql(insBarCodeSql, sqlParams);
        }


        /// <summary>
        /// 删除机壳二维码信息
        /// </summary>
        /// <returns></returns>
        public int deleteJKBarCode()
        {
            string delSql = "delete from [jk_barcode] ";
            return barcodeMdb.ExecuteSql(delSql, new OleDbParameter[] { });
        }


        /// <summary>
        /// 获取封箱条码信息
        /// </summary>
        /// <returns></returns>
        public DataTable getPackBarCodeList()
        {
            DataSet retSet = barcodeMdb.Query("select * from [pack_barcode]");
            if (retSet != null && retSet.Tables.Count > 0)
            {
                return retSet.Tables[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 备份封箱条码数据
        /// </summary>
        /// <param name="dtPack"></param>
        /// <param name="intAmount"></param>
        /// <returns></returns>
        public int backupPackData(DataTable dtPack, int intAmount)
        {

            try
            {

                int insCnt = 0;
                foreach (DataRow drPack in dtPack.Rows)
                {
                    // 存在数据直接更新
                    string updDelbzSql = "update [pack_barcode] set [delbz]='1' where [pack_barcodeID]=@pack_barcodeID and [delbz]='0'";
                    OleDbParameter updDelbzparam = new OleDbParameter("@pack_barcodeID", drPack["pack_barcodeID"]);
                    int updDelbzCnt = barcodeMdb.ExecuteNonQuery(updDelbzSql, updDelbzparam);
                    if (updDelbzCnt == 0)
                    {
                        LogUtil.Debug("备份MDB数据库不存在记录: pack_barcodeID=" + drPack["pack_barcodeID"]);
                    }

                    string EMPTY_CODE = "";
                    if (Convert.ToString(drPack["customer_name"]) == "合肥燃气集团")
                    {
                        EMPTY_CODE = "400000000000";
                    }
                    else if (Convert.ToString(drPack["customer_name"]) == "沈阳燃气公司")
                    {
                        EMPTY_CODE = "20000000000";
                    }
                    else
                    {
                        EMPTY_CODE = "200000000000";
                    }

                    for (int i = 0; i <= intAmount; i++)
                    {

                        string meter_barcodeID = Convert.ToString(drPack["meter" + (i + 1) + "_barcodeID"]);
                        if (meter_barcodeID != "" && meter_barcodeID != EMPTY_CODE)
                        {
                            string delSql = "update [pack_barcode] set [delbz]='1' where [meter1_barcodeID]=@meter_barcodeID and [delbz]='0' ";
                            OleDbParameter delParam = new OleDbParameter("@meter_barcodeID", meter_barcodeID);
                            int delCnt = barcodeMdb.ExecuteNonQuery(delSql, delParam);

                            delSql = "update [pack_barcode] set [delbz]='1' where [meter2_barcodeID]=@meter_barcodeID and [delbz]='0' ";
                            delParam = new OleDbParameter("@meter_barcodeID", meter_barcodeID);
                            delCnt = barcodeMdb.ExecuteNonQuery(delSql, delParam);

                            delSql = "update [pack_barcode] set [delbz]='1' where [meter3_barcodeID]=@meter_barcodeID and [delbz]='0' ";
                            delParam = new OleDbParameter("@meter_barcodeID", meter_barcodeID);
                            delCnt = barcodeMdb.ExecuteNonQuery(delSql, delParam);

                            delSql = "update [pack_barcode] set [delbz]='1' where [meter4_barcodeID]=@meter_barcodeID and [delbz]='0' ";
                            delParam = new OleDbParameter("@meter_barcodeID", meter_barcodeID);
                            delCnt = barcodeMdb.ExecuteNonQuery(delSql, delParam);

                            if (intAmount > 3)
                            {
                                delSql = "update [pack_barcode] set [delbz]='1' where [meter5_barcodeID]=@meter_barcodeID and [delbz]='0' ";
                                delParam = new OleDbParameter("@meter_barcodeID", meter_barcodeID);
                                delCnt = barcodeMdb.ExecuteNonQuery(delSql, delParam);


                                delSql = "update [pack_barcode] set [delbz]='1' where [meter6_barcodeID]=@meter_barcodeID and [delbz]='0' ";
                                delParam = new OleDbParameter("@meter_barcodeID", meter_barcodeID);
                                delCnt = barcodeMdb.ExecuteNonQuery(delSql, delParam);
                            }
                        }
                    }

                    string insPackSql = "INSERT INTO [pack_barcode] ([pack_barcodeID],[meter1_barcodeID],[meter2_barcodeID],[meter3_barcodeID],[meter4_barcodeID],[meter5_barcodeID],[meter6_barcodeID],[Operator_Name],[delbz],[OpDt],[customer_name]) VALUES (@pack_barcodeID,@meter1_barcodeID,@meter2_barcodeID,@meter3_barcodeID,@meter4_barcodeID,@meter5_barcodeID,@meter6_barcodeID,@Operator_Name,@delbz,@OpDt,@customer_name)";
                    OleDbParameter[] sqlParams = new OleDbParameter[11];
                    sqlParams[0] = new OleDbParameter("@pack_barcodeID", drPack["pack_barcodeID"]);
                    sqlParams[1] = new OleDbParameter("@meter1_barcodeID", drPack["meter1_barcodeID"]);
                    sqlParams[2] = new OleDbParameter("@meter2_barcodeID", drPack["meter2_barcodeID"]);
                    sqlParams[3] = new OleDbParameter("@meter3_barcodeID", drPack["meter3_barcodeID"]);
                    sqlParams[4] = new OleDbParameter("@meter4_barcodeID", drPack["meter4_barcodeID"]);
                    sqlParams[5] = new OleDbParameter("@meter5_barcodeID", drPack["meter5_barcodeID"]);
                    sqlParams[6] = new OleDbParameter("@meter6_barcodeID", drPack["meter6_barcodeID"]);
                    sqlParams[7] = new OleDbParameter("@Operator_Name", drPack["Operator_Name"]);
                    sqlParams[8] = new OleDbParameter("@delbz", drPack["delbz"]);
                    sqlParams[9] = new OleDbParameter("@OpDt", drPack["OpDt"]);
                    sqlParams[10] = new OleDbParameter("@customer_name", drPack["customer_name"]);
                    insCnt += barcodeMdb.ExecuteNonQuery(insPackSql, sqlParams);

                }
                LogUtil.Info("备份封箱条码信息插入数据：" + insCnt);
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                return Consts.RET_FAIL;
            }
           
            return Consts.RET_SUCCESS;
        }

        /// <summary>
        /// 删除封箱条码信息
        /// </summary>
        /// <returns></returns>
        public int deletePackBarCode()
        {
            string delSql = "delete from [pack_barcode] ";
            return barcodeMdb.ExecuteSql(delSql, new OleDbParameter[] { });
        }


        /// <summary>
        /// 保存封箱信息
        /// </summary>
        /// <returns>错误信息</returns>
        public string SavePackData(SavePackInfo packInfo)
        {
            try
            {
                // 存在数据直接更新
                string updDelbzSql = "update [pack_barcode] set [delbz]='1' where [pack_barcodeID]=@pack_barcodeID and [delbz]='0'";
                OleDbParameter updDelbzparam = new OleDbParameter("@pack_barcodeID", packInfo.pack_barcodeID);
                int updDelbzCnt = barcodeMdb.ExecuteNonQuery(updDelbzSql, updDelbzparam);
                if (updDelbzCnt == 0)
                {
                    LogUtil.Debug("本地MDB数据库不存在记录: pack_barcodeID=" + packInfo.pack_barcodeID);
                }

                for (int i = 0; i <= packInfo.intAmount; i++)
                {

                    string meter_barcodeID = packInfo.strCode[i];
                    if (meter_barcodeID != "" && meter_barcodeID != packInfo.EMPTY_CODE)
                    {

                        string existSql = "select 1 from [pack_barcode] where [delbz]='0' and ([meter1_barcodeID]=@meter_barcodeID or [meter2_barcodeID]=@meter_barcodeID or [meter3_barcodeID]=@meter_barcodeID or [meter4_barcodeID]=@meter_barcodeID ";
                        if (packInfo.intAmount == 5)
                        {
                            existSql = existSql + " or [meter5_barcodeID]=@meter_barcodeID or [meter6_barcodeID]=@meter_barcodeID ) ";
                        }
                        else
                        {
                            existSql = existSql + " ) ";
                        }
                        OleDbParameter existParam = new OleDbParameter("@meter_barcodeID", meter_barcodeID);
                        DataSet ds1 = barcodeMdb.Query(existSql, existParam);
                        if (ds1 != null && ds1.Tables.Count > 0 && ds1.Tables[0].Rows.Count > 0)
                        {
                            LogUtil.Error("封箱条码重复扫描！ meter_barcodeID=" + meter_barcodeID);
                            return "封箱条码重复扫描！";
                        }

                    }
                }

                string insPackSql = "INSERT INTO [pack_barcode] ([pack_barcodeID],[meter1_barcodeID],[meter2_barcodeID],[meter3_barcodeID],[meter4_barcodeID],[meter5_barcodeID],[meter6_barcodeID],[Operator_Name],[delbz],[OpDt],[customer_name]) VALUES (@pack_barcodeID,@meter1_barcodeID,@meter2_barcodeID,@meter3_barcodeID,@meter4_barcodeID,@meter5_barcodeID,@meter6_barcodeID,@Operator_Name,@delbz,@OpDt,@customer_name)";
                OleDbParameter[] sqlParams = new OleDbParameter[11];
                sqlParams[0] = new OleDbParameter("@pack_barcodeID", packInfo.pack_barcodeID);
                sqlParams[1] = new OleDbParameter("@meter1_barcodeID", packInfo.strCode[0]);
                sqlParams[2] = new OleDbParameter("@meter2_barcodeID", packInfo.strCode[1]);
                sqlParams[3] = new OleDbParameter("@meter3_barcodeID", packInfo.strCode[2]);
                sqlParams[4] = new OleDbParameter("@meter4_barcodeID", packInfo.strCode[3]);
                sqlParams[5] = new OleDbParameter("@meter5_barcodeID", packInfo.strCode[4]);
                sqlParams[6] = new OleDbParameter("@meter6_barcodeID", packInfo.strCode[5]);
                sqlParams[7] = new OleDbParameter("@Operator_Name", GlobalVal.CurrentUser.User);
                sqlParams[8] = new OleDbParameter("@delbz", "0");
                sqlParams[9] = new OleDbParameter("@OpDt", DateTime.Now.ToString());
                sqlParams[10] = new OleDbParameter("@customer_name", packInfo.CustomerName);
                int insCnt = barcodeMdb.ExecuteNonQuery(insPackSql, sqlParams);
                if (insCnt == 1)
                {
                    return "";
                }
                else
                {
                    return "封装信息保存失败！";
                }
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                return e.Message;
            }
        }

        /// <summary>
        /// 根据表号获取补打信息
        /// </summary>
        public bool getPrintInfoByBH(string strBH, int intAmount, PrintBarCodeInfo printInfo)
        {
            string existSql = "select * from [pack_barcode] where [delbz]='0' and ([meter1_barcodeID]=@meter_barcodeID or [meter2_barcodeID]=@meter_barcodeID or [meter3_barcodeID]=@meter_barcodeID or [meter4_barcodeID]=@meter_barcodeID ";
            if (intAmount == 5)
            {
                existSql = existSql + " or [meter5_barcodeID]=@meter_barcodeID or [meter6_barcodeID]=@meter_barcodeID) ";
            }
            else
            {
                existSql = existSql + " ) ";
            }
            OleDbParameter existParam = new OleDbParameter("@meter_barcodeID", strBH);
            DataSet retSet = barcodeMdb.Query(existSql, existParam);
            if (retSet != null && retSet.Tables.Count > 0 && retSet.Tables[0].Rows.Count > 0)
            {
                DataRow dr = retSet.Tables[0].Rows[0];
                string[] strCode = new string[6];
                strCode[0] = Convert.ToString(dr["meter1_barcodeID"]);
                strCode[1] = Convert.ToString(dr["meter2_barcodeID"]);
                strCode[2] = Convert.ToString(dr["meter3_barcodeID"]);
                strCode[3] = Convert.ToString(dr["meter4_barcodeID"]);
                strCode[4] = Convert.ToString(dr["meter5_barcodeID"]);
                strCode[5] = Convert.ToString(dr["meter6_barcodeID"]);
                printInfo.strCode = strCode;
                printInfo.packCode = Convert.ToString(dr["pack_barcodeID"]);
                printInfo.strCustomer = Convert.ToString(dr["customer_name"]);
                return true;
            }
            return false;
        }

        /// <summary>
        /// 根据箱号获取补打信息
        /// </summary>
        public bool getPrintInfoByXH(string strXH,PrintBarCodeInfo printInfo)
        {
            string existSql = "select * from [pack_barcode] where [delbz]='0' and [pack_barcodeID] = @pack_barcodeID";
            OleDbParameter existParam = new OleDbParameter("@pack_barcodeID", strXH);
            DataSet retSet = barcodeMdb.Query(existSql, existParam);

            if (retSet != null && retSet.Tables.Count > 0 && retSet.Tables[0].Rows.Count > 0)
            {
                DataRow dr = retSet.Tables[0].Rows[0];
                string[] strCode = new string[6];
                strCode[0] = Convert.ToString(dr["meter1_barcodeID"]);
                strCode[1] = Convert.ToString(dr["meter2_barcodeID"]);
                strCode[2] = Convert.ToString(dr["meter3_barcodeID"]);
                strCode[3] = Convert.ToString(dr["meter4_barcodeID"]);
                strCode[4] = Convert.ToString(dr["meter5_barcodeID"]);
                strCode[5] = Convert.ToString(dr["meter6_barcodeID"]);
                printInfo.strCode = strCode;
                printInfo.packCode = Convert.ToString(dr["pack_barcodeID"]);
                printInfo.strCustomer = Convert.ToString(dr["customer_name"]);
                return true;
            }
            return false;
        }

        public int deleteRepeatPack(int idx, string meter_barcodeID) 
        {
            string delSql = "delete from [pack_barcode] where [meter" + idx + "_barcodeID]=@meter_barcodeID and delbz='0' ";
            OleDbParameter delParam = new OleDbParameter("@meter_barcodeID", meter_barcodeID);
            return barcodeMdb.ExecuteSql(delSql, delParam);
        }

    }
}
